﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Matrix.Common.DBUtility;
using Matrix.MaKaYunShang.Model;
using Matrix.MaKaYunShang.Model.Dto;

namespace Matrix.MaKaYunShang.DAL
{
    public partial class CommodityLibDAL : BaseDAL<CommodityLibDAL>
    {
        #region 默认方法
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(CommodityLib model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into CommodityLib(");
            strSql.Append("CommodityName,HeadPic,SuggestedPrice,CeilingPrice,LowerPrice,BrandId,CateId,Unit,Spec,Description,HtmlIntroduce,ProducingAreaId,IsLanmaSold,HasGiftCommodity,GiftCount,GiftEndTime,IsVirtual,IsBatchSale,QuantityOfBatchSale,CommodityLibIdByBatch,SalesVolume,DisplaySort,BarCode,ShopCommission,AddTime,SupportPayWay,IsSupportDiscount,IsLanJianCommodity,IsOnlySingle,IsHeadquartersSend,IsShopCanViewOrder,IsShopCanModifyPrice,IsDelete");
            strSql.Append(") values (");
            strSql.Append("@CommodityName,@HeadPic,@SuggestedPrice,@CeilingPrice,@LowerPrice,@BrandId,@CateId,@Unit,@Spec,@Description,@HtmlIntroduce,@ProducingAreaId,@IsLanmaSold,@HasGiftCommodity,@GiftCount,@GiftEndTime,@IsVirtual,@IsBatchSale,@QuantityOfBatchSale,@CommodityLibIdByBatch,@SalesVolume,@DisplaySort,@BarCode,@ShopCommission,@AddTime,@SupportPayWay,@IsSupportDiscount,@IsLanJianCommodity,@IsOnlySingle,@IsHeadquartersSend,@IsShopCanViewOrder,@IsShopCanModifyPrice,@IsDelete");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@CommodityName", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@HeadPic", SqlDbType.NVarChar,300) ,            
                        new SqlParameter("@SuggestedPrice", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@CeilingPrice", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@LowerPrice", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@BrandId", SqlDbType.Int,4) ,            
                        new SqlParameter("@CateId", SqlDbType.Int,4) ,            
                        new SqlParameter("@Unit", SqlDbType.NVarChar,20) ,            
                        new SqlParameter("@Spec", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Description", SqlDbType.NVarChar,1000) ,            
                        new SqlParameter("@HtmlIntroduce", SqlDbType.Text) ,            
                        new SqlParameter("@ProducingAreaId", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsLanmaSold", SqlDbType.Bit,1) ,            
                        new SqlParameter("@HasGiftCommodity", SqlDbType.Bit,1) ,            
                        new SqlParameter("@GiftCount", SqlDbType.Int,4) ,            
                        new SqlParameter("@GiftEndTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@IsVirtual", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsBatchSale", SqlDbType.Bit,1) ,            
                        new SqlParameter("@QuantityOfBatchSale", SqlDbType.Int,4) ,            
                        new SqlParameter("@CommodityLibIdByBatch", SqlDbType.Int,4) ,            
                        new SqlParameter("@SalesVolume", SqlDbType.Int,4) ,            
                        new SqlParameter("@DisplaySort", SqlDbType.Int,4) ,            
                        new SqlParameter("@BarCode", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@ShopCommission", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@AddTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@SupportPayWay", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@IsSupportDiscount", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsLanJianCommodity", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsOnlySingle", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsHeadquartersSend", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsShopCanViewOrder", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsShopCanModifyPrice", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.CommodityName;
            parameters[1].Value = model.HeadPic;
            parameters[2].Value = model.SuggestedPrice;
            parameters[3].Value = model.CeilingPrice;
            parameters[4].Value = model.LowerPrice;
            parameters[5].Value = model.BrandId;
            parameters[6].Value = model.CateId;
            parameters[7].Value = model.Unit;
            parameters[8].Value = model.Spec;
            parameters[9].Value = model.Description;
            parameters[10].Value = model.HtmlIntroduce;
            parameters[11].Value = model.ProducingAreaId;
            parameters[12].Value = model.IsLanmaSold;
            parameters[13].Value = model.HasGiftCommodity;
            parameters[14].Value = model.GiftCount;
            parameters[15].Value = model.GiftEndTime;
            parameters[16].Value = model.IsVirtual;
            parameters[17].Value = model.IsBatchSale;
            parameters[18].Value = model.QuantityOfBatchSale;
            parameters[19].Value = model.CommodityLibIdByBatch;
            parameters[20].Value = model.SalesVolume;
            parameters[21].Value = model.DisplaySort;
            parameters[22].Value = model.BarCode;
            parameters[23].Value = model.ShopCommission;
            parameters[24].Value = model.AddTime;
            parameters[25].Value = model.SupportPayWay;
            parameters[26].Value = model.IsSupportDiscount;
            parameters[27].Value = model.IsLanJianCommodity;
            parameters[28].Value = model.IsOnlySingle;
            parameters[29].Value = model.IsHeadquartersSend;
            parameters[30].Value = model.IsShopCanViewOrder;
            parameters[31].Value = model.IsShopCanModifyPrice;
            parameters[32].Value = model.IsDelete;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {

                return Convert.ToInt32(obj);

            }

        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(CommodityLib model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update CommodityLib set ");

            strSql.Append(" CommodityName = @CommodityName , ");
            strSql.Append(" HeadPic = @HeadPic , ");
            strSql.Append(" SuggestedPrice = @SuggestedPrice , ");
            strSql.Append(" CeilingPrice = @CeilingPrice , ");
            strSql.Append(" LowerPrice = @LowerPrice , ");
            strSql.Append(" BrandId = @BrandId , ");
            strSql.Append(" CateId = @CateId , ");
            strSql.Append(" Unit = @Unit , ");
            strSql.Append(" Spec = @Spec , ");
            strSql.Append(" Description = @Description , ");
            strSql.Append(" HtmlIntroduce = @HtmlIntroduce , ");
            strSql.Append(" ProducingAreaId = @ProducingAreaId , ");
            strSql.Append(" IsLanmaSold = @IsLanmaSold , ");
            strSql.Append(" HasGiftCommodity = @HasGiftCommodity , ");
            strSql.Append(" GiftCount = @GiftCount , ");
            strSql.Append(" GiftEndTime = @GiftEndTime , ");
            strSql.Append(" IsVirtual = @IsVirtual , ");
            strSql.Append(" IsBatchSale = @IsBatchSale , ");
            strSql.Append(" QuantityOfBatchSale = @QuantityOfBatchSale , ");
            strSql.Append(" CommodityLibIdByBatch = @CommodityLibIdByBatch , ");
            strSql.Append(" SalesVolume = @SalesVolume , ");
            strSql.Append(" DisplaySort = @DisplaySort , ");
            strSql.Append(" BarCode = @BarCode , ");
            strSql.Append(" ShopCommission = @ShopCommission , ");
            strSql.Append(" AddTime = @AddTime , ");
            strSql.Append(" SupportPayWay = @SupportPayWay , ");
            strSql.Append(" IsSupportDiscount = @IsSupportDiscount , ");
            strSql.Append(" IsLanJianCommodity = @IsLanJianCommodity , ");
            strSql.Append(" IsOnlySingle = @IsOnlySingle , ");
            strSql.Append(" IsHeadquartersSend = @IsHeadquartersSend , ");
            strSql.Append(" IsShopCanViewOrder = @IsShopCanViewOrder , ");
            strSql.Append(" IsShopCanModifyPrice = @IsShopCanModifyPrice , ");
            strSql.Append(" IsDelete = @IsDelete  ");
            strSql.Append(" where Id=@Id ");

            SqlParameter[] parameters = {
			            new SqlParameter("@Id", SqlDbType.Int,4) ,            
                        new SqlParameter("@CommodityName", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@HeadPic", SqlDbType.NVarChar,300) ,            
                        new SqlParameter("@SuggestedPrice", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@CeilingPrice", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@LowerPrice", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@BrandId", SqlDbType.Int,4) ,            
                        new SqlParameter("@CateId", SqlDbType.Int,4) ,            
                        new SqlParameter("@Unit", SqlDbType.NVarChar,20) ,            
                        new SqlParameter("@Spec", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Description", SqlDbType.NVarChar,1000) ,            
                        new SqlParameter("@HtmlIntroduce", SqlDbType.Text) ,            
                        new SqlParameter("@ProducingAreaId", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsLanmaSold", SqlDbType.Bit,1) ,            
                        new SqlParameter("@HasGiftCommodity", SqlDbType.Bit,1) ,            
                        new SqlParameter("@GiftCount", SqlDbType.Int,4) ,            
                        new SqlParameter("@GiftEndTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@IsVirtual", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsBatchSale", SqlDbType.Bit,1) ,            
                        new SqlParameter("@QuantityOfBatchSale", SqlDbType.Int,4) ,            
                        new SqlParameter("@CommodityLibIdByBatch", SqlDbType.Int,4) ,            
                        new SqlParameter("@SalesVolume", SqlDbType.Int,4) ,            
                        new SqlParameter("@DisplaySort", SqlDbType.Int,4) ,            
                        new SqlParameter("@BarCode", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@ShopCommission", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@AddTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@SupportPayWay", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@IsSupportDiscount", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsLanJianCommodity", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsOnlySingle", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsHeadquartersSend", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsShopCanViewOrder", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsShopCanModifyPrice", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.Id;
            parameters[1].Value = model.CommodityName;
            parameters[2].Value = model.HeadPic;
            parameters[3].Value = model.SuggestedPrice;
            parameters[4].Value = model.CeilingPrice;
            parameters[5].Value = model.LowerPrice;
            parameters[6].Value = model.BrandId;
            parameters[7].Value = model.CateId;
            parameters[8].Value = model.Unit;
            parameters[9].Value = model.Spec;
            parameters[10].Value = model.Description;
            parameters[11].Value = model.HtmlIntroduce;
            parameters[12].Value = model.ProducingAreaId;
            parameters[13].Value = model.IsLanmaSold;
            parameters[14].Value = model.HasGiftCommodity;
            parameters[15].Value = model.GiftCount;
            parameters[16].Value = model.GiftEndTime;
            parameters[17].Value = model.IsVirtual;
            parameters[18].Value = model.IsBatchSale;
            parameters[19].Value = model.QuantityOfBatchSale;
            parameters[20].Value = model.CommodityLibIdByBatch;
            parameters[21].Value = model.SalesVolume;
            parameters[22].Value = model.DisplaySort;
            parameters[23].Value = model.BarCode;
            parameters[24].Value = model.ShopCommission;
            parameters[25].Value = model.AddTime;
            parameters[26].Value = model.SupportPayWay;
            parameters[27].Value = model.IsSupportDiscount;
            parameters[28].Value = model.IsLanJianCommodity;
            parameters[29].Value = model.IsOnlySingle;
            parameters[30].Value = model.IsHeadquartersSend;
            parameters[31].Value = model.IsShopCanViewOrder;
            parameters[32].Value = model.IsShopCanModifyPrice;
            parameters[33].Value = model.IsDelete;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("update CommodityLib set IsDelete=1 ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;


            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public bool DeleteList(string Idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update CommodityLib set IsDelete=1 ");
            strSql.Append(" where Id in (" + Idlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataSet GetModel(int Id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Id, CommodityName, HeadPic, SuggestedPrice, CeilingPrice, LowerPrice, BrandId, CateId, Unit, Spec, Description, HtmlIntroduce, ProducingAreaId, IsLanmaSold, HasGiftCommodity, GiftCount, GiftEndTime, IsVirtual, IsBatchSale, QuantityOfBatchSale, CommodityLibIdByBatch, SalesVolume, DisplaySort, BarCode, ShopCommission, AddTime, SupportPayWay, IsSupportDiscount, IsLanJianCommodity, IsOnlySingle, IsHeadquartersSend, IsShopCanViewOrder, IsShopCanModifyPrice, IsDelete  ");
            strSql.Append("  from CommodityLib ");
            strSql.Append(" where Id=@Id  and IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM CommodityLib where IsDelete=0 ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion

        #region 扩展方法

        /// <summary>
        /// 获取分页数据
        /// </summary>
        public List<ViewCommodityLibDto> GetPagingList(int pageIndex, int pageSize, string searchKeyWord, string colOrder, bool isHideShowLanmaSold, ref int totalCount, int shopId = 0)
        {
            string strWhere = " IsDelete = 0 ";

            if (!string.IsNullOrEmpty(searchKeyWord))
            {
                strWhere += " AND (CommodityName LIKE '%" + searchKeyWord + "%' OR Description LIKE '%" + searchKeyWord +
                            "%' OR SuggestedPrice LIKE '%" + searchKeyWord + "%' OR BrandName LIKE '%" + searchKeyWord +
                            "%' OR CategoryName LIKE '%" + searchKeyWord + "%') ";
            }

            if (isHideShowLanmaSold)
            {
                strWhere += " AND IsLanmaSold=0 ";
            }

            if (shopId > 0)
            {
                strWhere += " AND cl.Id NOT IN (SELECT CommodityLibId FROM ShopCommodity WHERE IsDelete=0 AND ShopId=" + shopId + ") ";
            }

            const string tables = "(SELECT  (case when cl.IsBatchSale = 'true' then '是' else '否' end) as IsBatchSaleStr, (case when cl.IsVirtual = 'true' then '是' else '否' end )as IsVirtualStr," +
                                  "cl.LowerPrice,cl.CeilingPrice,cl.IsVirtual,cl.IsBatchSale,cl.Id, cl.CommodityName, cl.HeadPic, cl.SuggestedPrice, cb.BrandName, cc.CategoryName, cl.Unit, cl.Spec, " +
                                  "cl.Description, cl.HtmlIntroduce, cl.ProducingAreaId, cl.IsLanmaSold, cl.HasGiftCommodity, cl.GiftCount, cl.GiftEndTime, " +
                                  "cl.SalesVolume, cl.BarCode, cl.ShopCommission, cl.AddTime, cl.SupportPayWay, cl.IsSupportDiscount, cl.IsLanJianCommodity, cl.IsOnlySingle, cl.IsHeadquartersSend, cl.IsShopCanViewOrder, cl.IsShopCanModifyPrice, cl.IsDelete " +
                                  "FROM CommodityLib cl " +
                                  "LEFT JOIN CommodityBrand cb ON cl.BrandId = cb.Id " +
                                  "LEFT JOIN CommodityCategory cc ON cl.CateId = cc.Id ) c ";

            colOrder = " IsLanmaSold DESC, " + colOrder;

            return GetSimpleListPage<ViewCommodityLibDto>(pageIndex, pageSize, tables, null, null, colOrder, strWhere, ref totalCount);
        }

        /// <summary>
        /// 获取商品全信息
        /// </summary>
        public DataSet GetCommodityFullInfo(int commodifyLibId)
        {
            var sbSql = new StringBuilder();

            sbSql.Append("SELECT cl.[Id],cl.[CommodityName],cl.[HeadPic],cl.[SuggestedPrice],cl.[BrandId],cl.[CateId], ");
            sbSql.Append("cl.[Unit],cl.[Spec],cl.[Description],cl.[HtmlIntroduce],cl.[ProducingAreaId],cl.[IsLanmaSold], ");
            sbSql.Append("cl.[HasGiftCommodity],cl.[GiftCount],cl.[GiftEndTime],cl.[SalesVolume],cl.[DisplaySort],cl.BarCode, cl.ShopCommission, cl.[AddTime], cl.SupportPayWay, cl.IsSupportDiscount, cl.IsLanJianCommodity, cl.IsOnlySingle, cl.IsHeadquartersSend, cl.IsShopCanViewOrder, cl.IsShopCanModifyPrice,cl.[IsDelete], ");
            sbSql.Append("cl.IsVirtual, cl.IsBatchSale, cl.CommodityLibIdByBatch, cl.QuantityOfBatchSale, ");
            sbSql.Append("cb.BrandName, cc.CategoryName, ai.AreaName AS ProducingAreaName ");
            sbSql.Append("FROM [CommodityLib] cl ");
            sbSql.Append("INNER JOIN [CommodityBrand] cb ON cl.BrandId=cb.Id ");
            sbSql.Append("INNER JOIN [CommodityCategory] cc ON cl.CateId=cc.Id ");
            sbSql.Append("LEFT JOIN [AreaInfo] ai ON cl.ProducingAreaId=ai.Id ");
            sbSql.Append("WHERE cl.Id=@CommodityLibId; ");

            sbSql.Append("SELECT cg.Id, cg.GiftCommodityLibId, cg.BelongToCommodityLibId, cg.GiftQuantity, cg.LeftQuantity, cg.IsDelete ");
            sbSql.Append("FROM CommodityGift cg ");
            sbSql.Append("WHERE cg.BelongToCommodityLibId=@CommodityLibId AND cg.IsDelete=0; ");

            sbSql.Append("SELECT cp.Id, cp.CommodityLibId, cp.PicUrl, cp.Usage, cp.DisplaySort, cp.IsDelete ");
            sbSql.Append("FROM CommodityPicture cp ");
            sbSql.Append("WHERE cp.CommodityLibId=@CommodityLibId AND cp.IsDelete=0; ");

            sbSql.Append("SELECT ccg.Id, ccg.CouponId, ccg.Quantity, ccg.BelongToCommodityLibId, ccg.IsDelete ");
            sbSql.Append("FROM CommodityCouponGift ccg ");
            sbSql.Append("WHERE ccg.BelongToCommodityLibId=@CommodityLibId AND ccg.IsDelete=0; ");

            SqlParameter[] parameters = {
					new SqlParameter("@CommodityLibId", SqlDbType.Int,4)
			};
            parameters[0].Value = commodifyLibId;

            var ds = DbHelperSQL.Query(sbSql.ToString(), parameters);
            if (ds != null && ds.Tables.Count > 0)
            {
                return ds;
            }
            return null;
        }

        /// <summary>
        /// 获取指定商品ID集合的商品全信息列表
        /// </summary>
        /// <param name="commodityIds">商品ID集合</param>
        public DataSet GetCommodityFullInfoAppointedIds(string commodityIds)
        {
            var sbSql = new StringBuilder();

            sbSql.Append("SELECT cl.[Id],cl.[CommodityName],cl.[HeadPic],cl.[SuggestedPrice],cl.[BrandId],cl.[CateId], ");
            sbSql.Append("cl.[Unit],cl.[Spec],cl.[Description],cl.[HtmlIntroduce],cl.[ProducingAreaId],cl.[IsLanmaSold], ");
            sbSql.Append("cl.[HasGiftCommodity],cl.[GiftCount],cl.[GiftEndTime],cl.[SalesVolume],cl.[DisplaySort],cl.BarCode,cl.ShopCommission,cl.[AddTime], cl.SupportPayWay, cl.IsSupportDiscount, cl.IsLanJianCommodity, cl.IsOnlySingle, cl.IsHeadquartersSend, cl.IsShopCanViewOrder, cl.IsShopCanModifyPrice,cl.[IsDelete], ");
            sbSql.Append("cl.IsVirtual, cl.IsBatchSale, cl.CommodityLibIdByBatch, cl.QuantityOfBatchSale, ");
            sbSql.Append("cb.BrandName, cc.CategoryName, ai.AreaName AS ProducingAreaName ");
            sbSql.Append("FROM [CommodityLib] cl ");
            sbSql.Append("INNER JOIN [CommodityBrand] cb ON cl.BrandId=cb.Id ");
            sbSql.Append("INNER JOIN [CommodityCategory] cc ON cl.CateId=cc.Id ");
            sbSql.Append("LEFT JOIN [AreaInfo] ai ON cl.ProducingAreaId=ai.Id ");
            sbSql.Append("WHERE cl.Id IN (" + commodityIds + ") AND cl.IsDelete=0; ");

            sbSql.Append("SELECT cg.Id, cg.GiftCommodityLibId, cg.BelongToCommodityLibId, cg.GiftQuantity, cg.LeftQuantity, cg.ConsumerGetTotalTimes, cg.TimeSpanCount, cg.TimeSpanType, cg.IsDelete, cl.CommodityName ");
            sbSql.Append("FROM CommodityGift cg ");
            sbSql.Append("INNER JOIN [CommodityLib] cl ON cl.Id=cg.GiftCommodityLibId ");
            sbSql.Append("WHERE cg.BelongToCommodityLibId IN (" + commodityIds + ") AND cg.IsDelete=0; ");

            sbSql.Append("SELECT cp.Id, cp.CommodityLibId, cp.PicUrl, cp.Usage, cp.DisplaySort, cp.IsDelete ");
            sbSql.Append("FROM CommodityPicture cp ");
            sbSql.Append("WHERE cp.CommodityLibId IN (" + commodityIds + ") AND cp.IsDelete=0; ");

            sbSql.Append("SELECT ccg.Id, ccg.CouponId, ccg.Quantity, ccg.BelongToCommodityLibId, ccg.IsDelete ");
            sbSql.Append("FROM CommodityCouponGift ccg ");
            sbSql.Append("WHERE ccg.BelongToCommodityLibId IN (" + commodityIds + ") AND ccg.IsDelete=0; ");

            var ds = DbHelperSQL.Query(sbSql.ToString());
            if (ds != null && ds.Tables.Count > 0)
            {
                return ds;
            }
            return null;
        }

        /// <summary>
        /// 通过店铺商品ID获取商品基本信息列表
        /// </summary>
        public List<CommodityLib> GetCommodityInfoByAppointedShopCommodityIds(string shopCommodityIds)
        {
            var sbSql = new StringBuilder();

            sbSql.Append("SELECT cl.[Id],cl.[CommodityName],cl.[HeadPic],cl.[SuggestedPrice],cl.[BrandId],cl.[CateId], ");
            sbSql.Append("cl.[Unit],cl.[Spec],cl.[Description],cl.[HtmlIntroduce],cl.[ProducingAreaId],cl.[IsLanmaSold], ");
            sbSql.Append("cl.[HasGiftCommodity],cl.[GiftCount],cl.[GiftEndTime],cl.[SalesVolume],cl.[DisplaySort],cl.BarCode,cl.ShopCommission,cl.[AddTime], cl.SupportPayWay, cl.IsSupportDiscount, cl.IsLanJianCommodity, cl.IsOnlySingle, cl.IsHeadquartersSend, cl.IsShopCanViewOrder, cl.IsShopCanModifyPrice,cl.[IsDelete], ");
            sbSql.Append("cl.IsVirtual, cl.IsBatchSale, cl.CommodityLibIdByBatch, cl.QuantityOfBatchSale, ");
            sbSql.Append("cb.BrandName, cc.CategoryName, ai.AreaName AS ProducingAreaName, sc.Id AS ShopCommodityId, sc.SoldPrice ");
            sbSql.Append("FROM [CommodityLib] cl ");
            sbSql.Append("INNER JOIN [CommodityBrand] cb ON cl.BrandId=cb.Id ");
            sbSql.Append("INNER JOIN [CommodityCategory] cc ON cl.CateId=cc.Id ");
            sbSql.Append("INNER JOIN [ShopCommodity] sc ON sc.CommodityLibId=cl.Id ");
            sbSql.Append("LEFT JOIN [AreaInfo] ai ON cl.ProducingAreaId=ai.Id ");
            sbSql.Append("WHERE sc.Id IN (" + shopCommodityIds + ") AND cl.IsDelete=0 AND sc.IsDelete=0 ");

            var lsRet = DbHelperSQL.ExecuteSqlList<CommodityLib>(sbSql.ToString());
            return lsRet != null && lsRet.Count > 0 ? lsRet.ToList() : null;
        }

        /// <summary>
        /// 根据条件获取库商品总数
        /// </summary>
        /// <remarks>2016-08-22 万乐园 创建</remarks>
        public int GetTotalCount(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select count(1) FROM CommodityLib where IsDelete=0");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }
            return int.Parse(DbHelperSQL.GetSingle(strSql.ToString()).ToString());
        }

        public DataSet GetListByCommodityName(string commodityName)
        {
            var strSql = new StringBuilder();
            strSql.Append("select c.* ,cb.BrandName");
            strSql.Append(" FROM CommodityLib c left join CommodityBrand cb on cb.Id=c.BrandId where c.IsDelete=0 ");
            if (!string.IsNullOrEmpty(commodityName))
            {
                strSql.Append(string.Format(" and CommodityName like '%{0}%'", commodityName));
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 根据shopCommodityIds获得数据列表
        /// </summary>
        public DataSet GetListByShopCommodityIds(string shopCommodityIds)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM CommodityLib where IsDelete=0 and Id in (select CommodityLibId from ShopCommodity where Id in (" + shopCommodityIds + "))");
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion
    }
}
